IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.RemoveIncomingFKs'))
BEGIN
	DROP PROCEDURE MD3.RemoveIncomingFKs
END
GO
/*
	Removes FKs that are referencing the given @TableName's PK.  

*/


CREATE PROCEDURE MD3.RemoveIncomingFKs
	 @TableName varchar(120)
	,@SchemaName varchar(120)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Tbl varchar(256), @Sch varchar(256), @ForeignKeyName varchar(256), @SQL varchar(max);

	SELECT 
		Const.ForeignKeyName	,	
		Const.SchemaName		,	
		Const.TableName		--,Const.*
	INTO #ConstraintsToDrop			
	FROM sys.objects AS o
	JOIN sys.schemas AS s 
		ON o.schema_id = s.schema_id
	CROSS APPLY MD3.f_GetForeignKeys(s.Name, o.Name) as Const
	WHERE o.type = 'U'
	AND Const.ParentSchemaName = @SchemaName
	AND Const.ParentTableName = @TableName
	ORDER BY 
		SCHEMA_NAME(o.schema_id),
		o.Name


	IF EXISTS (select * from #ConstraintsToDrop)
	BEGIN
		EXEC MD3.InsertLogMessage
			@Message = 'This table has foreign keys that reference the primary key.  We need to remove these to ALTER the PK. 
				Please rerun the FK scripts to ensure we do not mess up DRI, if you are running in a dev env.', 
			@ProcedureName = 'MD3.RemoveIncomingFKs', 
			@Severity = 'I';
		EXEC MD3.InsertLogMessage
			@Message = 'Also, depending on what you are changing with the PK, you may need to ALTER the Foreign Key metdata file.', 
			@ProcedureName = 'MD3.RemoveIncomingFKs', 
			@Severity = 'I';

		DECLARE Dave CURSOR FOR 
			SELECT 
				ForeignKeyName,
				SchemaName,
				TableName
			FROM #ConstraintsToDrop
		OPEN Dave
		FETCH NEXT FROM Dave INTO @ForeignKeyName,@Sch,@Tbl
		WHILE (@@FETCH_STATUS = 0)
		BEGIN
			SELECT @SQL = 'alter table ' + @Sch + '.' + @Tbl +' drop constraint '+@ForeignKeyName
			EXEC MD3.ExecuteSQL @SQL

			FETCH NEXT FROM Dave INTO @ForeignKeyName,@Sch,@Tbl
		END
		CLOSE Dave
		DEALLOCATE Dave
	END;
	
	RETURN;
END
GO